﻿using DAL.DataTransferObject;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;

namespace DAL.DataAccessObject
{
    public class NhanVienDAO : DataProvider.DataAccess
    {
        public void InsertNhanVien(NhanVien nv)
        {
            SqlCommand cmd = new SqlCommand("insert into NhanVien values" +
            " (@manv,@tenNV,@HoDemNV,@NS,@GT,@DC,@SDT,@Luong,@NLV,@ChucVu," +
            "@NQL,@Acc,@PW,@Level)", GetConnection());
            cmd.Parameters.Add(new SqlParameter("@manv", SqlDbType.VarChar)).Value = nv.maNV;
            cmd.Parameters.Add(new SqlParameter("@tennv", SqlDbType.NVarChar)).Value = nv.tenNV;
            cmd.Parameters.Add(new SqlParameter("@HoDemNV", SqlDbType.NVarChar)).Value = nv.hoDemNV;
            cmd.Parameters.Add(new SqlParameter("@NS", SqlDbType.DateTime)).Value = nv.ngaySinh;
            cmd.Parameters.Add(new SqlParameter("@GT", SqlDbType.Char)).Value = nv.GT;
            cmd.Parameters.Add(new SqlParameter("@DC", SqlDbType.NVarChar)).Value = nv.diaChi;
            cmd.Parameters.Add(new SqlParameter("@SDT", SqlDbType.VarChar)).Value = nv.SDT;
            cmd.Parameters.Add(new SqlParameter("@Luong", SqlDbType.Int)).Value = nv._luong;
            cmd.Parameters.Add(new SqlParameter("@NLV", SqlDbType.DateTime)).Value = nv.ngayLamViec;
            cmd.Parameters.Add(new SqlParameter("@ChucVu", SqlDbType.VarChar)).Value = nv.chucVu;
            cmd.Parameters.Add(new SqlParameter("@NQL", SqlDbType.VarChar)).Value = nv.maNQL;
            cmd.Parameters.Add(new SqlParameter("@Acc", SqlDbType.VarChar)).Value = nv._account;
            cmd.Parameters.Add(new SqlParameter("@PW", SqlDbType.VarChar)).Value = nv._password;
            cmd.Parameters.Add(new SqlParameter("@Level", SqlDbType.Int)).Value = nv._level;
            int flag = cmd.ExecuteNonQuery();
            if (flag == 1)
                MessageBox.Show("Nhập dữ liệu thành công !");
        }

        public void UpdateNhanVien(NhanVien nv)
        {
            //SqlCommand cmd = new SqlCommand("update NhanVien set TenNV=@tenNV, HoDemNV=@HoDemNV,NgaySinh=@NS, GT=@GT, DiaChi=@DC,Luong=@Luong,NgayLamViec@NLV,ChucVu=@ChucVu,MaNQL=@NQL,Account=@Acc,Password=@PW,CapDo=@Level where MaNV=@manv", GetConnection());
            SqlCommand cmd = new SqlCommand("update NhanVien set TenNV=@tenNV, HoDemNV=@HoDemNV,NgaySinh=@NS, GT=@GT, DiaChi=@DC,SDT=@SDT,Luong=@Luong,NgayLamViec=@NLV,ChucVu=@ChucVu,MaNQL=@NQL,Account=@Acc,Password=@PW,CapDo=@Level where MaNV=@manv", GetConnection());
            cmd.Parameters.Add(new SqlParameter("@manv", SqlDbType.Char)).Value = nv.maNV;
            cmd.Parameters.Add(new SqlParameter("@tenNV", SqlDbType.VarChar)).Value = nv.tenNV;
            cmd.Parameters.Add(new SqlParameter("@HoDemNV", SqlDbType.VarChar)).Value = nv.hoDemNV;
            cmd.Parameters.Add(new SqlParameter("@NS", SqlDbType.DateTime)).Value = nv.ngaySinh;
            cmd.Parameters.Add(new SqlParameter("@GT", SqlDbType.Char)).Value = nv.GT;
            cmd.Parameters.Add(new SqlParameter("@DC", SqlDbType.NVarChar)).Value = nv.diaChi;
            cmd.Parameters.Add(new SqlParameter("@SDT", SqlDbType.VarChar)).Value = nv.SDT;
            cmd.Parameters.Add(new SqlParameter("@Luong", SqlDbType.Int)).Value = nv._luong;
            cmd.Parameters.Add(new SqlParameter("@NLV", SqlDbType.DateTime)).Value = nv.ngayLamViec;
            cmd.Parameters.Add(new SqlParameter("@ChucVu", SqlDbType.VarChar)).Value = nv.chucVu;
            cmd.Parameters.Add(new SqlParameter("@NQL", SqlDbType.Char)).Value = nv.maNQL;
            cmd.Parameters.Add(new SqlParameter("@Acc", SqlDbType.VarChar)).Value = nv._account;
            cmd.Parameters.Add(new SqlParameter("@PW", SqlDbType.VarChar)).Value = nv._password;
            cmd.Parameters.Add(new SqlParameter("@Level", SqlDbType.Int)).Value = nv._level;
            if (cmd.ExecuteNonQuery() > 0)
                MessageBox.Show("Xử lý dữ liệu thành công !");
        }

        public void DeleteNhanVien(NhanVien nv)
        {
            nv._level = 0;
            this.UpdateNhanVien(nv);
        }

        public List<NhanVien> GetListNhanVien()
        {
            List<NhanVien> list = new List<NhanVien>();
            DataTable dt = this.SelectAllNhanVien();
            NhanVien nv;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                nv = new NhanVien();
                nv.maNV = dt.Rows[i]["MaNV"].ToString();
                nv.tenNV = dt.Rows[i]["TenNV"].ToString();
                nv.hoDemNV = dt.Rows[i]["HoDemNV"].ToString();
                nv.ngaySinh = Convert.ToDateTime(dt.Rows[i]["NgaySinh"].ToString());
                nv.ngayLamViec = Convert.ToDateTime(dt.Rows[i]["NgayLamViec"].ToString());
                nv.SDT = dt.Rows[i]["SDT"].ToString();
                nv.GT = dt.Rows[i]["GT"].ToString();
                nv.diaChi = dt.Rows[i]["DiaChi"].ToString();
                nv.chucVu = dt.Rows[i]["ChucVu"].ToString();
                nv._luong = Convert.ToInt32(dt.Rows[i]["Luong"].ToString());
                nv._account = dt.Rows[i]["Account"].ToString();
                nv._level = Convert.ToInt32(dt.Rows[i]["CapDo"].ToString());
                nv._password = dt.Rows[i]["Password"].ToString();
                nv.maNQL = dt.Rows[i]["MaNQL"].ToString();
                list.Add(nv);
            }
            return list;
        }

        public DataTable SelectAllNhanVien()
        {
            SqlCommand cmd = new SqlCommand("select * from NhanVien", GetConnection());
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public DataView SelectViewAllNhanVien()
        {
            SqlCommand cmd = new SqlCommand("select * from NhanVien", GetConnection());
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            DataView dv = new DataView(dt);
            return dv;
        }

        public DataView GetNhanVien(string maNV)
        {
            SqlCommand cmd = new SqlCommand("select * from NhanVien where maNV=@manv", GetConnection());
            cmd.Parameters.AddWithValue("@manv", maNV);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataView dv = new DataView();
            DataTable dt = new DataTable();
            dt.TableName = "demo";
            da.Fill(dt);
            dv.Table = dt;
            return dv;
        }
    }
}
